package com.ruoyi.system.service;

import com.alibaba.fastjson2.JSONObject;
import com.ruoyi.common.utils.MapPro;
import com.ruoyi.common.utils.StrKit;
import com.ruoyi.system.mapper.MapperTool;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.List;


@Component
public class ServiceTools {

	private static final String dataName = "warehouse";//查询数据库信息
	private static MapPro tableMap =new MapPro();//存储所有表信息的Map
	
	@Autowired
	MapperTool mt;

	/**
	 * 根据MAP查询数据
	 * @param tableName
	 * @param map
	 * @return
	 */
	public List<MapPro> selByMap(String tableName, MapPro map)
	{
		TableTool tt = (TableTool) tableMap.get(tableName.toLowerCase());
		MapPro columns = tt.columnMap;
		StringBuffer sql = new StringBuffer("select * from "+tableName+" where 1=1 ") ;

		for(String key : map.keySet())
		{
			if(!columns.containsKey(key))continue;
			if(columns.getStr(key).equals("varchar")||columns.getStr(key).equals("timestamp")){
				sql.append(" and ").append(key).append(" = '").append(fileStr(map.get(key))).append("' ");
			}else{
				sql.append(" and ").append(key).append(" = ").append(map.get(key));
			}
		}
		return mt.getList(sql.toString());
	}
	/**
	 * 根据MAP查询一条数据
	 * @param tableName
	 * @param map
	 * @return
	 */
	public MapPro selOneByMap(String tableName, MapPro map)
	{
		TableTool tt = (TableTool) tableMap.get(tableName.toLowerCase());
		MapPro columns = tt.columnMap;
		StringBuffer sql = new StringBuffer("select * from "+tableName+" where 1=1 ") ;
		
		for(String key : map.keySet())
		{
			if(!columns.containsKey(key))continue;
			if(columns.getStr(key).equals("varchar")||columns.getStr(key).equals("timestamp")){
				sql.append(" and ").append(key).append(" = '").append(fileStr(map.get(key))).append("' ");
			}else{
				sql.append(" and ").append(key).append(" = ").append(map.get(key));
			}
		}
		sql.append(" limit 1 ");
		return mt.getOne(sql.toString());
	}

	/**
	 * 根据Json查询一条数据
	 * @param tableName
	 * @param json
	 * @return
	 */
	public MapPro selOneByJson(String tableName, JSONObject json)
	{
		TableTool tt = (TableTool) tableMap.get(tableName.toLowerCase());
		MapPro columns = tt.columnMap;
		StringBuffer sql = new StringBuffer("select * from "+tableName+" where 1=1 ") ;

		for(String key : json.keySet())
		{
			if(!columns.containsKey(key))continue;
			if(columns.getStr(key).equals("varchar")||columns.getStr(key).equals("timestamp")){
				sql.append(" and ").append(key).append(" = '").append(fileStr(json.get(key))).append("' ");
			}else{
				sql.append(" and ").append(key).append(" = ").append(json.get(key));
			}
		}
		sql.append(" limit 1 ");
		return mt.getOne(sql.toString());
	}

	/**
	 * 根据ID查询数据
	 * @param tableName
	 * @param id
	 * @return
	 */
	public MapPro getById(String tableName, Object id)
	{
		id=fileStr(id);
		TableTool tt = (TableTool) tableMap.get(tableName.toLowerCase());
		String sql = "select * from "+tableName+" where "+tt.priKey+" = "+id;
		System.out.println(" getbyid sql ::::::::::: "+sql);
		return mt.getOne(sql);
	}
	
	/**
	 * 根据ID删除数据
	 * @param tableName
	 * @param id
	 * @return
	 */
	public int delById(String tableName,Object id)
	{
		id=fileStr(id);
		TableTool tt = (TableTool) tableMap.get(tableName.toLowerCase());
		String sql = "delete from "+tableName+" where "+tt.priKey+" = "+id;
		return mt.update(sql);
	}
	
	/**
	 * 根据map修改数据
	 * @param tableName
	 * @param map
	 * @return
	 */
	public int updateByMap(String tableName, MapPro map)
	{
		TableTool tt = (TableTool) tableMap.get(tableName.toLowerCase());
		MapPro columns = tt.columnMap;
		String prikey = tt.priKey;
		//判断如果有没有主键，返回0
		if(StrKit.isBlank(tt.priKey)||!map.containsKey(tt.priKey))return 0;
		//封装sql
//		StringBuffer sql = new StringBuffer("update "+tableName+" set  "+tt.priKey+" = "+tt.priKey);//下方语句=本条语句
		StringBuffer sql = new StringBuffer("update ").append(tableName).append(" set ").append(prikey).append(" = ").append(map.get(prikey));
		for(String key : map.keySet())
		{
			//判断表里是否有本字段
			if(!columns.containsKey(key))continue;
			//如果是字符串\时间戳类型，加个引号
			if(columns.getStr(key).equals("varchar")||columns.getStr(key).equals("timestamp")){
				sql.append(",").append(key).append(" = '").append(fileStr(map.get(key))).append("' ");
			}else{
				sql.append(",").append(key).append(" = ").append(map.get(key));
			}
			
		}
		sql.append(" where ").append(prikey).append("=").append(map.get(prikey));
		String upsql = sql.toString();
		System.out.println(upsql);
		return mt.update(upsql);
	}
	
	/**
	 * 插入数据
	 * @param tableName
	 * @param map
	 * @return
	 */
	public MapPro insDataGetId(String tableName, MapPro map)
	{
		TableTool tt = (TableTool) tableMap.get(tableName.toLowerCase());
		MapPro columns = tt.columnMap;
		StringBuffer keysb = new StringBuffer("(");
		StringBuffer valsb = new StringBuffer("(");
		for(String key : map.keySet())
		{
			//判断表里是否有本字段
			if(!columns.containsKey(key))continue;
			//封装key
			if(keysb.length()>2)keysb.append(",");
			keysb.append(key);
			//封装VAL
			if(valsb.length()>2)valsb.append(",");
			if(map.get(key) instanceof String){
				valsb.append("'").append(map.get(key)).append("'");
			}else{
				valsb.append(map.get(key));
			}
			
			
		}
		keysb.append(")");
		valsb.append(")");
		String sql = "insert into "+tableName+keysb+" values "+valsb;
		System.out.println(sql);
		mt.insData(sql);
		return map;
	}
	
	/**
	 * 插入数据
	 * @param tableName
	 * @param map
	 * @return
	 */
	public int insData(String tableName, MapPro map)
	{
		TableTool tt = (TableTool) tableMap.get(tableName.toLowerCase());
		MapPro columns = tt.columnMap;
		StringBuffer keysb = new StringBuffer("(");
		StringBuffer valsb = new StringBuffer("(");
		for(String key : map.keySet())
		{
			//判断表里是否有本字段
			if(!columns.containsKey(key))continue;
			//封装key
			if(keysb.length()>2)keysb.append(",");
			keysb.append(key);
			//封装VAL
			if(valsb.length()>2)valsb.append(",");
			if(map.get(key) instanceof String){
				valsb.append("'").append(map.get(key)).append("'");
			}else{
				valsb.append(map.get(key));
			}
			
			
		}
		keysb.append(")");
		valsb.append(")");
		String sql = "insert into "+tableName+keysb+" values "+valsb;
		
		System.out.println(sql);
		return mt.update(sql);
	}

	/**
	 * 初始化table,将所有table字段加入集合
	 */
	@PostConstruct
	private void initTable()
	{
		System.out.println("初始化数据库！！！！！！");
		List<MapPro> tables = mt.getAllTable(dataName);
		for(int i = 0;i<tables.size();i++)
		{
			MapPro table = tables.get(i);
			//像tablMap中存数据
			//没有就建立新MAP
			String tableName = ((String) table.get("TABLE_NAME")).toLowerCase();//表名
			String columnName = ((String) table.get("COLUMN_NAME")).toLowerCase();//列名
			String dataType = ((String) table.get("DATA_TYPE")).toLowerCase();//列类型
			String key = table.get("COLUMN_KEY")!=null?((String) table.get("COLUMN_KEY")).toLowerCase():"";//列类型
			if(!tableMap.containsKey(tableName))
			{
				TableTool tt = new TableTool();
				tableMap.put(tableName, tt);
			}
			//像table中加入本字段的值
			TableTool tt = (TableTool) tableMap.get(tableName);
			tt.columnMap.put(columnName, dataType);
			if(key.equals("pri")||columnName.equals("id"))tt.priKey=columnName;//默认ID就是主键。如果有指定主键也行
			
		}
	}
	
	/**
	 * 封装table的类
	 * @author dell
	 *
	 */
	private class TableTool{
		public String priKey ;//主键名称
		public MapPro columnMap = new MapPro();//列信息
	}
	/**
	 * 如果入参是字符串，则过滤特殊字符
	 * @param o
	 * @return
	 */
	private Object fileStr(Object o)
	{
		if(o instanceof String){
			o=((String)o).replaceAll("\"", "")// 匹配双引号
					.replaceAll("\'", "")// 匹配单引号
					.replaceAll("\\(.*?\\)", "")// 匹配左右括号
					.replaceAll("[+]", "")// 匹配加号		
					.replaceAll(";", "");// 匹配;
		}
		
		return o;
	}
}

